﻿using CSharp.Libs.Database;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Reflection;

namespace CSharp.Libs.Media
{
    public class DataAccessLayer : IDisposable
    {
        #region declarations
        private SQLiteHandler _sqLiteHandler;

        #region events
        public event DataAccessLayerMessageEventDelegate DataAccessLayerMessageEvent;
        protected void ThrowDataAccessLayerMessageEvent(
            DataAccessLayerMessageEventArgs ea)
        {
            if (DataAccessLayerMessageEvent != null)
            {
                DataAccessLayerMessageEvent(this, ea);
            }
        }
        #endregion
        #endregion



        #region constructor
        public DataAccessLayer()
        {
            //use fix file name
            _sqLiteHandler = new SQLiteHandler(string.Concat(
                Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location),
                "\\PicLib.s3db"));

            //Dictionary<string, object> dic = new Dictionary<string, object>();
            //dic.Add("BasePathId", (object)0);
            //dic.Add("FullFileName", (object)@"c:\Bilder\Test1.jpg");
            //dic.Add("FileName", (object)@"Test1.jpg");
            //dic.Add("Path", (object)@"c:\Bilder");
            //dic.Add("Hash", (object)@"XAKJLMWE'ÜÄ?JLFS");
            //dic.Add("Comment",(object)"Test gut ++ comment");
            //_sqLiteHandler._writeDataToTable("tblPictureObjects", dic);
        }

        #region dispose pattern (http://www.codeproject.com/Articles/15360/Implementing-IDisposable-and-the-Dispose-Pattern-P)
        // some fields that require cleanup
        private bool disposed = false; // to detect redundant calls

        protected virtual void Dispose(bool disposing)
        {
            if (!disposed)
            {
                if (disposing)
                {
                    // clean up managed handles
                    _sqLiteHandler.Dispose();
                    _sqLiteHandler = null;
                }

                disposed = true;
            }
        }

        public void Dispose()
        {
            Dispose(true);
        }
        #endregion
        #endregion



        #region event handling
        #endregion



        #region private functions
        private List<TagObject> _getExistingTags()
        {
            string strSqlTags = "SELECT * FROM tblTags ORDER BY Tag";
            List<TagObject> lTags = new List<TagObject>();

            DataTable dtTags = _sqLiteHandler.GetDataTable(strSqlTags);

            int intTagId;
            foreach (DataRow drTag in dtTags.Rows)
            {
                if (int.TryParse(drTag["TagId"].ToString(), out intTagId))
                {
                    lTags.Add(new TagObject() { TagId = intTagId, Tag = drTag["Tag"].ToString() });
                }
            }

            return lTags;
        }

        private List<PictureObject> _getExistingPictureObjects()
        {
            string strSql = "SELECT * FROM tblPictureObjects";
            List<PictureObject> lPictureObjects = new List<PictureObject>();

            DataTable dtPOs = _sqLiteHandler.GetDataTable(strSql);

            int intPOId;
            foreach (DataRow drPO in dtPOs.Rows)
            {
                if (int.TryParse(drPO["PictureObjectId"].ToString(), out intPOId))
                {
                    int intBasePathId = 0;
                    int.TryParse(drPO["BasePathId"].ToString(), out intBasePathId);

                    PictureObject po = new PictureObject();
                    po.PictureObjectId = intPOId;
                    po.BasePathId = intBasePathId;
                    po.FullFileName = drPO["FullFileName"].ToString();
                    po.FileName = drPO["FileName"].ToString();
                    po.Path = drPO["Path"].ToString();
                    po.Hash = drPO["Hash"].ToString();
                    po.Comment = drPO["Comment"].ToString();

                    lPictureObjects.Add(po);

                    _getPictureObjectData(po);
                }
            }

            return lPictureObjects;
        }

        private void _getPictureObjectData(
            PictureObject PicObj)
        {
            if (!string.IsNullOrEmpty(PicObj.Hash))
            {
                string strSql = "SELECT * FROM tblPictureObjects WHERE Hash = '" + PicObj.Hash + "'";
                if (_sqLiteHandler.HasRows(strSql))
                {
                    strSql = "SELECT tblTags.TagId, tblTags.Tag FROM tblPictureObjects, tblPictureObjectTags, tblTags WHERE tblPictureObjects.PictureObjectId = tblPictureObjectTags.PictureObjectId AND tblPictureObjectTags.TagId = tblTags.TagId AND (tblPictureObjects.Hash = '" + PicObj.Hash + "')";
                    DataTable dtTags = _sqLiteHandler.GetDataTable(strSql);

                    if (dtTags.Rows.Count > 0)
                    {
                        int intTagId;

                        foreach (DataRow drTag in dtTags.Rows)
                        {
                            if (int.TryParse(drTag["TagId"].ToString(), out intTagId))
                            {
                                PicObj.Tags.Add(new TagObject() { TagId = intTagId, Tag = drTag["Tag"].ToString() });
                            }
                        }
                    }
                }
            }
            else
            {
                //No Hash given
            }
        }

        private PictureObject _getPictureObjectById(
            string PictureObjectId)
        {
            string strSql = "SELECT * FROM tblPictureObjects WHERE PictureObjectId = " + PictureObjectId;
            DataTable dtPOs = _sqLiteHandler.GetDataTable(strSql);

            if (dtPOs == null || dtPOs.Rows.Count != 1)
            {
                return null;
            }

            int intPOId;
            foreach (DataRow drPO in dtPOs.Rows)
            {
                if (int.TryParse(drPO["PictureObjectId"].ToString(), out intPOId))
                {
                    int intBasePathId = 0;
                    int.TryParse(drPO["BasePathId"].ToString(), out intBasePathId);
                    PictureObject po = new PictureObject();
                    po.PictureObjectId = intPOId;
                    po.BasePathId = intBasePathId;
                    po.FullFileName = drPO["FullFileName"].ToString();
                    po.FileName = drPO["FileName"].ToString();
                    po.Path = drPO["Path"].ToString();
                    po.Hash = drPO["Hash"].ToString();
                    po.Comment = drPO["Comment"].ToString();

                    _getPictureObjectData(po);

                    return po;
                }
            }

            return null;
        }

        private void _writePictureObjectData(
            PictureObject PicObj)
        {
            Dictionary<string, object> dic = new Dictionary<string, object>();
            dic.Add("BasePathId", (object)0);
            dic.Add("FullFileName", (object)PicObj.FullFileName);
            dic.Add("FileName", (object)PicObj.FileName);
            dic.Add("Path", (object)PicObj.Path);
            dic.Add("Hash", (object)PicObj.Hash);
            dic.Add("Comment", (object)PicObj.Comment);
            _sqLiteHandler.WriteDataToTable("tblPictureObjects", dic);
        }

        private void _deletePictureObject(
            PictureObject PicObj)
        {
            string strSql = "DELETE FROM tblPictureObjects WHERE PictureObjectId = " + PicObj.PictureObjectId.ToString();
            bool fDeleteSuccess = _sqLiteHandler.ExecuteSql(strSql);

            DataAccessLayerMessageEventArgs ea = new DataAccessLayerMessageEventArgs();
            if (fDeleteSuccess)
            {
                ea.Type = DataAccessLayerMessageType.DeleteSuccessful;
                ea.Message = "Deleted PictureObjectId: " + PicObj.PictureObjectId.ToString();
                ThrowDataAccessLayerMessageEvent(ea);
            }
            else
            {
                ea.Type = DataAccessLayerMessageType.DeleteError;
                ea.Message = "Error deleting PictureObjectId: " + PicObj.PictureObjectId.ToString();
                ThrowDataAccessLayerMessageEvent(ea);
            }
        }

        private void _addTagToPictureObject()
        {
        }

        private void _removeTagFromPictureObject()
        {
        }

        private void _createTag(
            TagObject NewTag)
        {
            string strSql = "SELECT * FROM tblTags WHERE Tag LIKE '" + NewTag.Tag + "'";
            DataTable dtTag = _sqLiteHandler.GetDataTable(strSql);

            if (dtTag.Rows.Count < 1)
            {
                int intRetVal = _sqLiteHandler.WriteDataToTable("tblTags", new Dictionary<string, object>() { { "Tag", (object)NewTag.Tag } });
            }
            else if (dtTag.Rows.Count == 1)
            {
                //Message it exits
                ThrowDataAccessLayerMessageEvent(new DataAccessLayerMessageEventArgs() { Type = DataAccessLayerMessageType.EntryExists, Message = "Tag already exists." });
            }
            else if (dtTag.Rows.Count > 1)
            {
                //Message multiple entries
                //Database reorganize useful
                ThrowDataAccessLayerMessageEvent(new DataAccessLayerMessageEventArgs() { Type = DataAccessLayerMessageType.MultipleEntries, Message = "Tag exists multiple times. Execute Database cleanup." });
            }
        }

        private void _deleteTag(
            TagObject DeleteTag)
        {
            string strSql = "DELETE FROM tblTags WHERE TagId = " + DeleteTag.TagId.ToString();
            bool fDeleteSuccess = _sqLiteHandler.ExecuteSql(strSql);

            DataAccessLayerMessageEventArgs ea = new DataAccessLayerMessageEventArgs();
            if (fDeleteSuccess)
            {
                ea.Type = DataAccessLayerMessageType.DeleteSuccessful;
                ea.Message = "Deleted TagId: " + DeleteTag.TagId.ToString();
                ThrowDataAccessLayerMessageEvent(ea);
            }
            else
            {
                ea.Type = DataAccessLayerMessageType.DeleteError;
                ea.Message = "Error deleting TagId: " + DeleteTag.TagId.ToString();
                ThrowDataAccessLayerMessageEvent(ea);
            }
        }

        private void _updateTag(
            TagObject UpdateTag)
        {
            string strSql = "UPDATE tblTags SET Tag = '" + UpdateTag.Tag + "' WHERE TagId = " + UpdateTag.TagId.ToString();
            bool fDeleteSuccess = _sqLiteHandler.ExecuteSql(strSql);

            DataAccessLayerMessageEventArgs ea = new DataAccessLayerMessageEventArgs();
            if (fDeleteSuccess)
            {
                ea.Type = DataAccessLayerMessageType.UpdateSuccessful;
                ea.Message = "Updated TagId: " + UpdateTag.TagId.ToString();
                ThrowDataAccessLayerMessageEvent(ea);
            }
            else
            {
                ea.Type = DataAccessLayerMessageType.UpdateError;
                ea.Message = "Error updating TagId: " + UpdateTag.TagId.ToString();
                ThrowDataAccessLayerMessageEvent(ea);
            }
        }
        #endregion



        #region methods
        public List<TagObject> GetExistingTags()
        {
            return _getExistingTags();
        }

        public List<PictureObject> GetExistingPictureObjects()
        {
            return _getExistingPictureObjects();
        }

        public void GetPictureObjectData(
            PictureObject PicObj)
        {
            _getPictureObjectData(PicObj);
        }

        public PictureObject GetPictureObjectById(
            string PictureObjectId)
        {
            return _getPictureObjectById(PictureObjectId);
        }

        public void WritePictureObjectData(
            PictureObject PicObj)
        {
            _writePictureObjectData(PicObj);
        }

        public void DeletePictureObject(
            PictureObject PicObj)
        {
            _deletePictureObject(PicObj);
        }

        public void CreateTag(
            TagObject NewTag)
        {
            _createTag(NewTag);
        }

        public void DeleteTag(
            TagObject DeleteTag)
        {
            _deleteTag(DeleteTag);
        }

        public void UpdateTag(
            TagObject UpdateTag)
        {
            _updateTag(UpdateTag);
        }
        #endregion



        #region properties
        #endregion
    }



    #region associated enums
    public enum DataAccessLayerMessageType
    {
        EntryExists = 1,
        MultipleEntries,
        DeleteSuccessful,
        DeleteError,
        UpdateSuccessful,
        UpdateError,
    }
    #endregion



    #region event stuff
    #region         delegates
    public delegate void DataAccessLayerMessageEventDelegate(object sender, DataAccessLayerMessageEventArgs ea);
    #endregion

    #region         event args
    public class DataAccessLayerMessageEventArgs : EventArgs
    {
        public DataAccessLayerMessageType Type { get; set; }
        public string Message { get; set; }
    }
    #endregion
    #endregion
}
